package com.employee.dao;

import com.employee.entity.Option;
import com.employee.util.DBUtil;
import com.employee.util.IdGenerator;
import com.employee.util.ResultSetMapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 选项表DAO
 */
public class OptionDao {
    
    /**
     * 分页查询选项（支持名称和范畴组合查询）
     */
    public Map<String, Object> findByPage(int pageNum, int pageSize, String name, String category) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        PreparedStatement countStmt = null;
        ResultSet rs = null;
        ResultSet countRs = null;
        
        Map<String, Object> result = new HashMap<>();
        
        try {
            conn = DBUtil.getConnection();
            
            // 构建查询条件
            StringBuilder whereSql = new StringBuilder(" WHERE 1=1 ");
            List<Object> params = new ArrayList<>();
            
            if (name != null && !name.trim().isEmpty()) {
                whereSql.append(" AND name LIKE ? ");
                params.add("%" + name.trim() + "%");
            }
            
            if (category != null && !category.trim().isEmpty()) {
                whereSql.append(" AND category LIKE ? ");
                params.add("%" + category.trim() + "%");
            }
            
            // 查询总记录数
            String countSql = "SELECT COUNT(*) FROM t_option" + whereSql.toString();
            countStmt = conn.prepareStatement(countSql);
            for (int i = 0; i < params.size(); i++) {
                countStmt.setObject(i + 1, params.get(i));
            }
            countRs = countStmt.executeQuery();
            int total = 0;
            if (countRs.next()) {
                total = countRs.getInt(1);
            }
            
            // 分页查询数据
            String sql = "SELECT * FROM t_option" + whereSql.toString() + 
                        " ORDER BY create_time DESC LIMIT ?, ?";
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }
            pstmt.setInt(params.size() + 1, (pageNum - 1) * pageSize);
            pstmt.setInt(params.size() + 2, pageSize);
            
            rs = pstmt.executeQuery();
            List<Option> list = ResultSetMapper.map(rs, Option.class);
            
            result.put("list", list);
            result.put("total", total);
            result.put("pageNum", pageNum);
            result.put("pageSize", pageSize);
            
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, countStmt, countRs);
            DBUtil.close(conn, pstmt, rs);
        }
        
        return result;
    }
    
    /**
     * 根据范畴查询所有选项（用于下拉框）
     */
    public List<Option> findByCategory(String category) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM t_option WHERE category = ? ORDER BY value";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, category);
            
            rs = pstmt.executeQuery();
            return ResultSetMapper.map(rs, Option.class);
            
        } catch (Exception e) {
            e.printStackTrace();
            return new ArrayList<>();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
    }
    
    /**
     * 根据ID查询选项
     */
    public Option findById(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM t_option WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            
            rs = pstmt.executeQuery();
            List<Option> list = ResultSetMapper.map(rs, Option.class);
            return list.isEmpty() ? null : list.get(0);
            
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
    }
    
    /**
     * 新增选项
     */
    public boolean insert(Option option) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO t_option (id, name, category, value, remark) VALUES (?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            
            // 生成主键ID
            option.setId(IdGenerator.generateId("OPT"));
            
            pstmt.setString(1, option.getId());
            pstmt.setString(2, option.getName());
            pstmt.setString(3, option.getCategory());
            pstmt.setString(4, option.getValue());
            pstmt.setString(5, option.getRemark());
            
            return pstmt.executeUpdate() > 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }
    
    /**
     * 更新选项
     */
    public boolean update(Option option) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE t_option SET name=?, category=?, value=?, remark=? WHERE id=?";
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, option.getName());
            pstmt.setString(2, option.getCategory());
            pstmt.setString(3, option.getValue());
            pstmt.setString(4, option.getRemark());
            pstmt.setString(5, option.getId());
            
            return pstmt.executeUpdate() > 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }
    
    /**
     * 删除选项
     */
    public boolean delete(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM t_option WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            
            return pstmt.executeUpdate() > 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }
    
    /**
     * 批量删除选项
     */
    public boolean deleteBatch(String[] ids) {
        if (ids == null || ids.length == 0) {
            return false;
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            StringBuilder sql = new StringBuilder("DELETE FROM t_option WHERE id IN (");
            for (int i = 0; i < ids.length; i++) {
                sql.append("?");
                if (i < ids.length - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
            
            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < ids.length; i++) {
                pstmt.setString(i + 1, ids[i]);
            }
            
            return pstmt.executeUpdate() > 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DBUtil.close(conn, pstmt);
        }
    }
}
